﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

using SWE4.Data.EmbeddedSensorCloud.DTO.Request;
using SWE4.Data.EmbeddedSensorCloud.DTO.Response;
using MySql.Data.MySqlClient;

namespace EmbeddedSensorCloud.Plugin.MicroERP
{
    public class MySQLInvoiceDAO : MySQLDAOBase, IInvoiceDAO
    {
        #region Constructor

        public MySQLInvoiceDAO(string connectionString)
            : base(connectionString)
        { }

        #endregion

        #region IInvoiceDAO

        public uint Create(InvoiceRequestModel invoice)
        {
            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    string invoiceInsertSQL = "INSERT INTO `invoices` (`issueDate`, `dueDate`, `invoiceNumber`, `comment`, `message`, `contactId_fk`) VALUES (@issueDate, @dueDate, @invoiceNumber, @comment, @message, @contactId);";
                    
                    MySqlCommand invoiceInsertCommand = new MySqlCommand(invoiceInsertSQL, connection, transaction);
                    invoiceInsertCommand.Prepare();
                    invoiceInsertCommand.Parameters.AddWithValue("@issueDate", invoice.IssueDate);
                    invoiceInsertCommand.Parameters.AddWithValue("@dueDate", invoice.DueDate);
                    invoiceInsertCommand.Parameters.AddWithValue("@invoiceNumber", invoice.Number);
                    invoiceInsertCommand.Parameters.AddWithValue("@comment", invoice.Comment);
                    invoiceInsertCommand.Parameters.AddWithValue("@message", invoice.Message);
                    invoiceInsertCommand.Parameters.AddWithValue("@contactId", invoice.CustomerId);
                    invoiceInsertCommand.ExecuteNonQuery();

                    uint lastId = base.GetLastInsertId(connection, transaction);
                    InsertInvoiceItems(invoice.Items, lastId, connection, transaction);

                    transaction.Commit();
                    return lastId;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public IEnumerable<InvoiceResponseModel> Read(string customerQuery, DateTime? minDate, DateTime? maxDate, decimal? minSum, decimal? maxSum)
        {
            StringBuilder invoiceSQLBuilder = new StringBuilder("SELECT i.`id`, i.`issueDate`, i.`dueDate`, i.`invoiceNumber`, i.`sum`, i.`contactId_fk`, p.`firstname`, p.`lastname`, c.`name` FROM `invoices` i LEFT JOIN `persons` p ON i.`contactId_fk` = p.`id` LEFT JOIN `companies` c ON i.`contactId_fk` = c.`id` WHERE");
            Dictionary<string, bool> existingParameters = new Dictionary<string, bool>
            {
                {"customer", false},
                {"minDate", false},
                {"maxDate", false},
                {"minSum", false},
                {"maxSum", false}
            };

            if (!string.IsNullOrWhiteSpace(customerQuery))
            {
                invoiceSQLBuilder.Append("(c.`name` LIKE @contactName OR p.`firstname` LIKE @contactName OR p.`lastname` LIKE @contactName)");
                existingParameters["customer"] = true;
            }
            if (minDate != null)
            {
                if (existingParameters.Any(p => p.Value))
                { invoiceSQLBuilder.Append(" AND"); }

                invoiceSQLBuilder.Append(" i.`issueDate` >= @minDate");
                existingParameters["minDate"] = true;
            }
            if (maxDate != null)
            {
                if (existingParameters.Any(p => p.Value))
                { invoiceSQLBuilder.Append(" AND"); }

                invoiceSQLBuilder.Append(" i.`issueDate` <= @maxDate");
                existingParameters["maxDate"] = true;
            }
            if (minSum != null)
            {
                if (existingParameters.Any(p => p.Value))
                { invoiceSQLBuilder.Append(" AND"); }

                invoiceSQLBuilder.Append(" i.`sum` >= @minSum");
                existingParameters["minSum"] = true;
            }
            if (maxSum != null)
            {
                if (existingParameters.Any(p => p.Value))
                { invoiceSQLBuilder.Append(" AND"); }

                invoiceSQLBuilder.Append(" i.`sum` <= @maxSum");
                existingParameters["maxSum"] = true;
            }
            string invoiceSQL = invoiceSQLBuilder.ToString();

            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                try
                {
                    List<InvoiceResponseModel> invoices = new List<InvoiceResponseModel>();

                    MySqlCommand invoiceFilterCommand = new MySqlCommand(invoiceSQL, connection, transaction);
                    invoiceFilterCommand.Prepare();

                    if (existingParameters["customer"]) { invoiceFilterCommand.Parameters.AddWithValue("@contactName", string.Format("%{0}%", customerQuery)); }
                    if (existingParameters["minDate"]) { invoiceFilterCommand.Parameters.AddWithValue("@minDate", minDate.Value); }
                    if (existingParameters["maxDate"]) { invoiceFilterCommand.Parameters.AddWithValue("@maxDate", maxDate.Value); }
                    if (existingParameters["minSum"]) { invoiceFilterCommand.Parameters.AddWithValue("@minSum", minSum.Value); }
                    if (existingParameters["maxSum"]) { invoiceFilterCommand.Parameters.AddWithValue("@maxSum", maxSum.Value); }

                    using (MySqlDataReader reader = invoiceFilterCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            InvoiceResponseModel invoice = new InvoiceResponseModel()
                            {
                                Id = reader.GetUInt32("id"),
                                IssueDate = reader.GetDateTime("issueDate"),
                                DueDate = reader.GetDateTime("dueDate"),
                                Number = reader.GetString("invoiceNumber"),
                                Sum = reader.GetDecimal("sum")
                            };

                            if (!reader.IsDBNull(reader.GetOrdinal("firstname")))
                            {
                                invoice.Customer = new PersonResponseModel()
                                {
                                    Id = reader.GetUInt32("contactId_fk"),
                                    FirstName = reader.GetString("firstname"),
                                    LastName = reader.GetString("lastname")
                                };
                            }
                            else
                            {
                                invoice.Customer = new CompanyResponseModel()
                                {
                                    Id = reader.GetUInt32("contactId_fk"),
                                    Name = reader.GetString("name")
                                };
                            }
                            invoices.Add(invoice);
                        }
                    }

                    transaction.Rollback();
                    return invoices;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public InvoiceResponseModel Read(uint invoiceId)
        {
            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted))
            {
                try
                {
                    string invoiceSQL = "SELECT i.`id`, i.`issueDate`, i.`dueDate`, i.`invoiceNumber`, i.`comment`, i.`message`, i.`sum`, i.`contactId_fk`, p.`firstname`, p.`lastname`, c.`name` FROM `invoices` i LEFT JOIN `persons` p ON i.`contactId_fk` = p.`id` LEFT JOIN `companies` c ON i.`contactId_fk` = c.`id` WHERE i.`id` = @invoiceId;";
                    string invoiceItemsSQL = "SELECT `id`, `name`, `unitPrice`, `amount`, `salesTax` FROM `invoiceitems` WHERE `invoiceId_fk` = @invoiceId;";
                    InvoiceResponseModel invoice = null;

                    MySqlCommand invoiceCommand = new MySqlCommand(invoiceSQL, connection, transaction);
                    invoiceCommand.Prepare();
                    invoiceCommand.Parameters.AddWithValue("@invoiceId", invoiceId);

                    using (MySqlDataReader reader = invoiceCommand.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (reader.Read())
                        {
                            invoice = new InvoiceResponseModel()
                            {
                                Id = reader.GetUInt32("id"),
                                IssueDate = reader.GetDateTime("issueDate"),
                                DueDate = reader.GetDateTime("dueDate"),
                                Number = reader.GetString("invoiceNumber"),
                                Sum = reader.GetDecimal("sum")
                            };
                            if (!reader.IsDBNull(reader.GetOrdinal("comment"))) { invoice.Comment = reader.GetString("comment"); }
                            if (!reader.IsDBNull(reader.GetOrdinal("message"))) { invoice.Message = reader.GetString("message"); }

                            if (!reader.IsDBNull(reader.GetOrdinal("firstname")))
                            {
                                invoice.Customer = new PersonResponseModel()
                                {
                                    Id = reader.GetUInt32("contactId_fk"),
                                    FirstName = reader.GetString("firstname"),
                                    LastName = reader.GetString("lastname")
                                };
                            }
                            else
                            {
                                invoice.Customer = new CompanyResponseModel()
                                {
                                    Id = reader.GetUInt32("contactId_fk"),
                                    Name = reader.GetString("name")
                                };
                            }
                        }
                    }

                    MySqlCommand invoiceItemsCommand = new MySqlCommand(invoiceItemsSQL, connection, transaction);
                    invoiceItemsCommand.Prepare();
                    invoiceItemsCommand.Parameters.AddWithValue("@invoiceId", invoiceId);

                    List<InvoiceItemResponseModel> invoiceItems = new List<InvoiceItemResponseModel>();
                    using(MySqlDataReader reader = invoiceItemsCommand.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            invoiceItems.Add(new InvoiceItemResponseModel()
                            {
                                Id = reader.GetUInt32("id"),
                                Name = reader.GetString("name"),
                                UnitPrice = reader.GetDecimal("unitPrice"),
                                Amount = reader.GetUInt32("amount"),
                                Tax = reader.GetDecimal("salesTax")
                            });
                        }
                    }
                    invoice.Items = invoiceItems;

                    transaction.Rollback();
                    return invoice;
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public void Update(InvoiceRequestModel invoice, uint invoiceId)
        {
            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    string invoiceUpdateSQL = "UPDATE `invoices` SET `issueDate` = @issueDate, `dueDate` = @dueDate, `invoiceNumber` = @invoiceNumber, `comment` = @comment, `message` = @message, `contactId_fk` = @contactId WHERE `id` = @id;";
                    //string invoiceItemUpdateSQL = "UPDATE `invoiceitems` SET `unitPrice` = @unitPrice, `amount` = @amount, `salesTax` = @salesTax WHERE `id` = @id;";
                    string invoiceItemsDeleteSQL = "DELETE FROM `invoiceitems` WHERE `invoiceId_fk` = @invoiceId;";

                    MySqlCommand invoiceUpdateCommand = new MySqlCommand(invoiceUpdateSQL, connection, transaction);
                    invoiceUpdateCommand.Prepare();
                    invoiceUpdateCommand.Parameters.AddWithValue("@id", invoiceId);
                    invoiceUpdateCommand.Parameters.AddWithValue("@issueDate", invoice.IssueDate);
                    invoiceUpdateCommand.Parameters.AddWithValue("@dueDate", invoice.DueDate);
                    invoiceUpdateCommand.Parameters.AddWithValue("@invoiceNumber", invoice.Number);
                    invoiceUpdateCommand.Parameters.AddWithValue("@comment", invoice.Comment);
                    invoiceUpdateCommand.Parameters.AddWithValue("@message", invoice.Message);
                    invoiceUpdateCommand.Parameters.AddWithValue("@contactId", invoice.CustomerId);
                    invoiceUpdateCommand.ExecuteNonQuery();

                    MySqlCommand invoiceItemsDeleteCommand = new MySqlCommand(invoiceItemsDeleteSQL, connection, transaction);
                    invoiceItemsDeleteCommand.Prepare();
                    invoiceItemsDeleteCommand.Parameters.AddWithValue("@invoiceId", invoiceId);
                    invoiceItemsDeleteCommand.ExecuteNonQuery();

                    InsertInvoiceItems(invoice.Items, invoiceId, connection, transaction);

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        public void Delete(uint invoiceId)
        {
            using (MySqlConnection connection = base.GetConnection())
            using (MySqlTransaction transaction = connection.BeginTransaction(IsolationLevel.Serializable))
            {
                try
                {
                    string invoiceDeleteSQL = "DELETE FROM `invoices` WHERE `id` = @id;";

                    MySqlCommand invoiceDeleteCommand = new MySqlCommand(invoiceDeleteSQL, connection, transaction);
                    invoiceDeleteCommand.Prepare();
                    invoiceDeleteCommand.Parameters.AddWithValue("@id", invoiceId);
                    invoiceDeleteCommand.ExecuteNonQuery();

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        #endregion

        private void InsertInvoiceItems(IEnumerable<InvoiceItemRequestModel> items, uint invoiceId, MySqlConnection connection, MySqlTransaction transaction)
        {
            string invoiceItemsInsertSQL = "INSERT INTO `invoiceitems` (`name`, `unitPrice`, `amount`, `salesTax`, `invoiceId_fk`) VALUES (@name, @unitPrice, @amount, @salesTax, @invoiceId);";

            MySqlCommand invoiceItemsInsertCommand = new MySqlCommand(invoiceItemsInsertSQL, connection, transaction);
            invoiceItemsInsertCommand.Prepare();

            foreach (InvoiceItemRequestModel invoiceItem in items)
            {
                invoiceItemsInsertCommand.Parameters.Clear();
                invoiceItemsInsertCommand.Parameters.AddWithValue("@name", invoiceItem.Name);
                invoiceItemsInsertCommand.Parameters.AddWithValue("@unitPrice", invoiceItem.UnitPrice);
                invoiceItemsInsertCommand.Parameters.AddWithValue("@amount", invoiceItem.Amount);
                invoiceItemsInsertCommand.Parameters.AddWithValue("@salesTax", invoiceItem.Tax);
                invoiceItemsInsertCommand.Parameters.AddWithValue("@invoiceId", invoiceId);
                invoiceItemsInsertCommand.ExecuteNonQuery();
            }
        }
    }
}
